﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Odbc;
using System.Data.OleDb;

namespace NSATBPortal
{

    public class ATBSchemaDB : IDisposable
    {
        private SqlConnection g_sqlConn;
        private bool _disposed;

        public ATBSchemaDB()
        {
            System.Configuration.Configuration rootWebConfig =
                System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~/");
            System.Configuration.ConnectionStringSettings connString;
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings["NSMATBSQLServer"];

            g_sqlConn = new
                SqlConnection(connString.ConnectionString);
            g_sqlConn.Open();
            _disposed = false;
        }

        public DataTable GetDefTestCaseList(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestCaseList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestCaseID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public DataTable GetDefTestCaseList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestCaseList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public void DeleteDefTestCase(int pTestCaseID, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalDeleteTestCase", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestCaseID", SqlDbType.Int);
                param.Value = pTestCaseID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
        }
        public DataTable GetDefTestCaseSteps(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestCaseSteps", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestCaseID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }

        public DataTable GetDefTestSetList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestSetList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }

        public DataTable GetDefTestSetAssignments(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestSetTestCaseAssignments", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public void ModifyDefTestSetAssignment(int pID, int pTCID, bool bNewState, string sUpdatedBy)
        {
            if (bNewState)
            {
                using (SqlCommand cmd = new SqlCommand("dbo.PortalAssignTestSetTestCaseByID", g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@TestSetID", SqlDbType.Int);
                    param.Value = pID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@TestCaseID", SqlDbType.Int);
                    param.Value = pTCID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                    param.Value = sUpdatedBy;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteScalar();
                }
            }
            else
            {
                using (SqlCommand cmd = new SqlCommand("dbo.PortalRemoveTestSetTestCase", g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@TestSetID", SqlDbType.Int);
                    param.Value = pID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@TestCaseID", SqlDbType.Int);
                    param.Value = pTCID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                    param.Value = sUpdatedBy;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteScalar();
                }
            }

        }
        public DataTable GetDefTestSetOrdering(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestSetTestCaseList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public void DefTestSetOrderMoveUp(int pTestSetCasesID)
        {
            using (SqlCommand cmd = new SqlCommand("dbo.PortalTestSetTestCaseUpLevel", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetCasesID", SqlDbType.Int);
                param.Value = pTestSetCasesID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;

        }
        public void DefTestSetOrderMoveDown(int pTestSetCasesID)
        {
            using (SqlCommand cmd = new SqlCommand("dbo.PortalTestSetTestCaseDownLevel", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetCasesID", SqlDbType.Int);
                param.Value = pTestSetCasesID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;

        }
        public DataTable GetDefTestSetDataSet(int pTestSetCasesID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestSetTestCaseDataSet", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetCasesID", SqlDbType.Int);
                param.Value = pTestSetCasesID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public void ModifyDefTestSetDataSet(int pTestSetCasesID, int pTestDataSetID, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand("dbo.PortalAssignDataSetForTestSetTestCase", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetCasesID", SqlDbType.Int);
                param.Value = pTestSetCasesID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestDataSetID", SqlDbType.Int);
                if (pTestDataSetID > 0)
                    param.Value = pTestDataSetID;
                else
                    param.Value = DBNull.Value;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            return;

        }
        public int CreateDefTestSet(string sName, int iExecutionOrder, int iMachineOp, string sDescription, string sCreatedBy)
        {
            const string constSQL = @"dbo.PortalAddTestSet";

            int iOutIdentity = 0;

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetName", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ExecutionOrder", SqlDbType.Int);
                param.Value = iExecutionOrder;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@MachineOp", SqlDbType.Int);
                param.Value = iMachineOp;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sCreatedBy;
                cmd.Parameters.Add(param);

                SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                paramOutIdentity.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramOutIdentity);

                cmd.ExecuteNonQuery();

                iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
            }
            return iOutIdentity;

        }

        public DataTable GetDefTestSetRawDetails(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestSetEdit", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestSetID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public DataTable GetDefTestSetColExecutionOrderValuesList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestColExecutionOrderValuesList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public DataTable GetDefTestSetColMachineOpValuesList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestColMachineOpValuesList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }

        public void UpdateDefTestSet(int pID, string sName, int iExecutionOrder, int iMachineOp, string sDescription, string sUpdatedBy)
        {
            const string constSQL = @"dbo.PortalUpdateTestSet";

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@DefTestSetID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestSetName", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ExecutionOrder", SqlDbType.Int);
                param.Value = iExecutionOrder;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@MachineOp", SqlDbType.Int);
                param.Value = iMachineOp;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

            }
            return;

        }

        public DataTable GetTestDataSetList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestDataList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetDefTestDataSetList(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestDataList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public DataTable GetDefTestDataSetDetail(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestDataDetail", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }

        public DataTable GetDefBaseStateList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetBaseStateList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetDefBaseStateParamList(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetBaseStateParamList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@BaseStateID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public void DefBaseStateRemoveParam(int pParamID, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalBaseStateDeleteParam", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ParamID", SqlDbType.Int);
                param.Value = pParamID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }
        public void DefBaseStateUpdateParam(int pBaseStateID, string sParamName, string sParamValue, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalBaseStateAddParam", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@BaseStateID", SqlDbType.Int);
                param.Value = pBaseStateID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ParamValue", SqlDbType.VarChar);
                param.Value = sParamValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ParamName", SqlDbType.VarChar);
                param.Value = sParamName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }
        public DataTable GetParamsAvailForDefBaseState(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetBaseStateOpenParamList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@BaseStateID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetBaseStateParamValidValues(string sParamName)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetBaseStateParamValidValues", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ParamName", SqlDbType.VarChar);
                param.Value = sParamName;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public string GetBaseStateParamDefaultValue(string sParamName)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetBaseStateParamDefaultValue", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ParamName", SqlDbType.VarChar);
                param.Value = sParamName;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            if (dtResults.Rows.Count == 0)
                return "";
            else
                return dtResults.Rows[0].Field<string>("DefaultValue").ToString();
        }

        public int CreateDefBaseState(string sName, string sDescription, int iCopyFromID, string sCreatedBy)
        {
            const string constSQL = @"dbo.PortalAddBaseState";

            int iOutIdentity = 0;

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sCreatedBy;
                cmd.Parameters.Add(param);

                if(iCopyFromID > 0)
                {
                    param = new SqlParameter("@CopyItemsFromID", SqlDbType.Int);
                    param.Value = iCopyFromID;
                    cmd.Parameters.Add(param);
                }

                SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                paramOutIdentity.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramOutIdentity);

                cmd.ExecuteNonQuery();

                iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
            }
            return iOutIdentity;

        }
        public void DeleteDefBaseState(int pID, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalDeleteBaseState", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@BaseStateID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }

        public DataTable GetTestMachinesList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestMachineList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public void AddTestMachine(string sAddress, bool bIsVirtual, string sVirtualName, int iVMMaxGuests, string sDetails, bool bMachineState, string sUpdatedBy)
        {
            UpdateTestMachine(0, sAddress, bIsVirtual, sVirtualName, iVMMaxGuests, sDetails, bMachineState, sUpdatedBy);
        }
        public void UpdateTestMachine(int pID, string sAddress, bool bIsVirtual, string sVirtualName, int iVMMaxGuests, string sDetails, bool bMachineState, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalAddTestMachine", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                if (0 != pID)
                {
                    param = new SqlParameter("@TestMachineID", SqlDbType.Int);
                    param.Value = pID;
                    cmd.Parameters.Add(param);
                }

                param = new SqlParameter("@MachineAddress", SqlDbType.VarChar);
                param.Value = sAddress;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@IsVirtual", SqlDbType.Bit);
                param.Value = bIsVirtual;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@VirtualName", SqlDbType.VarChar);
                if( sVirtualName.Trim().Length > 0 )
                    param.Value = sVirtualName;
                else
                    param.Value = DBNull.Value;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@VirtualMaxGuests", SqlDbType.Int);
                if (iVMMaxGuests > 0)
                    param.Value = iVMMaxGuests;
                else
                    param.Value = DBNull.Value;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Details", SqlDbType.VarChar);
                param.Value = sDetails;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@MachineState", SqlDbType.Bit);
                param.Value = bMachineState;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }
        public void ModifyTestMachineState(int pID, bool bState, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalModifyTestMachineState", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@MachineState", SqlDbType.Bit);
                param.Value = bState;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestMachineID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }

        public DataTable GetTestMachineGroupList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestMachineGroupList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }

        public DataTable GetDefMachineGroupAssignments(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand("dbo.PortalGetTestMachineGroupMembers", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@TestMachineGroupID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public void ModifyDefMachineGroupAssignment(int pID, int pMachineID, bool bNewState, string sUpdatedBy)
        {
            if (bNewState)
            {
                using (SqlCommand cmd = new SqlCommand("dbo.PortalAssignTestMachineGroupItemByID", g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@TestMachineGroupID", SqlDbType.Int);
                    param.Value = pID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@TestMachineID", SqlDbType.Int);
                    param.Value = pMachineID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                    param.Value = sUpdatedBy;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteScalar();
                }
            }
            else
            {
                using (SqlCommand cmd = new SqlCommand("dbo.PortalRemoveTestMachineGroupItem", g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@TestMachineGroupID", SqlDbType.Int);
                    param.Value = pID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@TestMachineID", SqlDbType.Int);
                    param.Value = pMachineID;
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                    param.Value = sUpdatedBy;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteScalar();
                }
            }

        }
        public int CreateDefMachineGroup(string sName, string sDescription, string sCreatedBy)
        {
            const string constSQL = @"dbo.PortalAddTestMachineGroup";

            int iOutIdentity = 0;

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sCreatedBy;
                cmd.Parameters.Add(param);

                SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                paramOutIdentity.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramOutIdentity);

                cmd.ExecuteNonQuery();

                iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
            }
            return iOutIdentity;

        }

        public DataTable GetNotificationGroupList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetNotificationGroupList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetNotificationGroupItemList(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetNotificationGroupItemList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@NotificationGroupID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }


        public DataTable GetDefRunList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetDefRunList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetDefRunItemList(int pID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalDefRunItemList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = pID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetDefRunItemDetail(int pItemID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalDefRunItemDetail", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ItemID", SqlDbType.Int);
                param.Value = pItemID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public int CreateDefRun(string sName, string sDescription, int iNotifyID, string sCreatedBy)
        {
            const string constSQL = @"dbo.PortalAddDefRun";

            int iOutIdentity = 0;

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@NotificationGroupID", SqlDbType.Int);
                if (iNotifyID == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = iNotifyID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sCreatedBy;
                cmd.Parameters.Add(param);

                SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                paramOutIdentity.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramOutIdentity);

                cmd.ExecuteNonQuery();

                iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
            }
            return iOutIdentity;

        }
        public void CreateDefRunItem(int iRunID, int iTestSetID, int iBaseStateID, int iTestMachineGroup, string sCreatedBy)
        {
            const string constSQL = @"dbo.PortalDefRunUpdateItem";

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunID", SqlDbType.Int);
                param.Value = iRunID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestSetID", SqlDbType.Int);
                param.Value = iTestSetID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@BaseStateID", SqlDbType.Int);
                param.Value = iBaseStateID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestMachineGroupID", SqlDbType.Int);
                param.Value = iTestMachineGroup;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sCreatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;

        }
        public void DefRunItemRemoveItem(int iItemID, string sUpdatedBy)
        {
            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalDefRunDeleteItem", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = iItemID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
        }

        public DataTable GetRunsList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetRunsList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsDetail(int iID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetRunsDetail", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = iID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }

        public void CreateRuns(string sName, int iDefRunID, DateTime dtScheduled, string sUpdatedBy)
        {
            UpdateRuns(0, sName, iDefRunID, dtScheduled, sUpdatedBy);
        }
        public void UpdateRuns(int iID, string sName, int iDefRunID, DateTime dtScheduled, string sUpdatedBy)
        {
            const string constSQL = @"dbo.PortalUpdateRuns";

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                if(iID > 0)
                {
                    param = new SqlParameter("@ID", SqlDbType.Int);
                    param.Value = iID;
                    cmd.Parameters.Add(param);
                }

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@DefRunID", SqlDbType.Int);
                param.Value = iDefRunID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Scheduled", SqlDbType.DateTime);
                param.Value = dtScheduled;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();

            }
            return;

        }
        public void DeleteRuns(int iID, string sUpdatedBy)
        {
            const string constSQL = @"dbo.PortalDeleteRuns";

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = iID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;

        }
        public void DeleteRunsJournal(int iID, string sUpdatedBy)
        {
            const string constSQL = @"dbo.PortalDeleteRunsJournal";

            using (SqlCommand cmd = new SqlCommand(constSQL, g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@UpdatedBy", SqlDbType.VarChar);
                param.Value = sUpdatedBy;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;

        }
        public DataTable GetRunsJournalList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetCompletedRunsList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalItemDetail(int iRunsID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetCompletedRunsTestCaseList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalTestCaseDetail(int iResTestCaseID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetCompletedTestCase", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestCaseID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalTestCaseStepsData(int iResTestCaseID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetCompletedTestStepsData", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestCaseID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalTestCaseSteps(int iResTestCaseID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetCompletedTestCaseSteps", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestCaseID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalTestCaseEvidenceList(int iResTestCaseID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetTestResultEvidence", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestCaseID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetRunsJournalTestStepEvidenceList(int iResTestStepID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.PortalGetStepResultEvidence", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestStepID", SqlDbType.Int);
                param.Value = iResTestStepID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public SqlCommand GetEvidenceBinaryData(int iResTestEvidenceID)
        {

            SqlCommand cmd = new SqlCommand(@"dbo.PortalGetResultEvidenceBinary", g_sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param;

            param = new SqlParameter("@ResTestEvidenceID", SqlDbType.Int);
            param.Value = iResTestEvidenceID;
            cmd.Parameters.Add(param);

            return cmd;
        }





        public string ImportDefTestCase(string sFilename, string sCreatedBy)
        {
            DataTable dtTestCaseName = new DataTable();
            DataTable dtQCID = new DataTable();
            DataTable dtDescription = new DataTable();
            DataTable dtBaseState = new DataTable();
            DataTable dtSteps = new DataTable();
            string sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sFilename + "';Extended Properties='Excel 12.0 Xml;HDR=No';";
            using(OleDbConnection Con = new System.Data.OleDb.OleDbConnection(sql))
            {
                try
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        Con.Open();
                        cmd.Connection = Con;

                        // Get Test Case Name
                        cmd.CommandText = "SELECT * FROM [TestCase$B1:B1]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtTestCaseName.Load(objDataReader);
                        }

                        // Get Test Case QCID
                        cmd.CommandText = "SELECT * FROM [TestCase$B2:B2]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtQCID.Load(objDataReader);
                        }

                        // Get Test Case Description
                        cmd.CommandText = "SELECT * FROM [TestCase$A4:E4]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtDescription.Load(objDataReader);
                        }

                        // Get Test Case Maximum Run Time
                        cmd.CommandText = "SELECT * FROM [TestCase$B5:B5]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtBaseState.Load(objDataReader);
                        }

                        // Get Steps
                        cmd.CommandText = "SELECT * FROM [TestCase$A7:R100]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtSteps.Load(objDataReader);
                        }
                        Con.Close();
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }

            // First create the import identifier row
            const string constNewImportTCSQL = @"dbo.PortalCreateImportTCRow";
            int iOutIdentity = 0;
            try
            {
                using (SqlCommand cmd = new SqlCommand(constNewImportTCSQL, g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@Name", SqlDbType.VarChar);
                    param.Value = dtTestCaseName.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@QCID", SqlDbType.VarChar);
                    param.Value = dtQCID.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@Description", SqlDbType.VarChar, 2048);
                    param.Value = dtDescription.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@BaseState", SqlDbType.VarChar);
                    param.Value = dtBaseState.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                    param.Value = sCreatedBy;
                    cmd.Parameters.Add(param);

                    SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                    paramOutIdentity.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paramOutIdentity);

                    cmd.ExecuteNonQuery();

                    iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            // Now fill out the steps
            const string constNewImportTCItemSQL = @"dbo.PortalAddImportItem";
            try
            {

                int iColCount = dtSteps.Columns.Count;
                if (iColCount > 18)
                    iColCount = 18;
                int iRowIndex = 0;
                while (iRowIndex < dtSteps.Rows.Count)
                {
                    using (SqlCommand cmd = new SqlCommand(constNewImportTCItemSQL, g_sqlConn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter param;
                        param = new SqlParameter("@ID", SqlDbType.Int);
                        param.Value = iOutIdentity;
                        cmd.Parameters.Add(param);
                        // First handle top row (step name/param names)
                        {
                            int iColIndex = 0;
                            while (iColIndex < iColCount)
                            {
                                param = new SqlParameter("@T" + (iColIndex + 1).ToString(), SqlDbType.VarChar);
                                param.Value = dtSteps.Rows[iRowIndex][iColIndex].ToString();
                                cmd.Parameters.Add(param);
                                iColIndex++;
                            }
                        }
                        iRowIndex++;
                        // Now handle bottom row (action word name/param values)
                        {
                            int iColIndex = 0;
                            while (iColIndex < iColCount)
                            {
                                param = new SqlParameter("@I" + (iColIndex + 1).ToString(), SqlDbType.VarChar);
                                param.Value = dtSteps.Rows[iRowIndex][iColIndex].ToString();
                                cmd.Parameters.Add(param);
                                iColIndex++;
                            }
                        }

                        cmd.ExecuteNonQuery();
                    }
                    iRowIndex++;
                }

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            // Now add the actual test case
            const string constDoImportTCSQL = @"dbo.PortalImportTestCase";
            try
            {
                using (SqlCommand cmd = new SqlCommand(constDoImportTCSQL, g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@ImportTableID", SqlDbType.Int);
                    param.Value = iOutIdentity;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }


            // Add New Test Case to Import Table
            return "Success";


        }

        public string ImportDefTestDataSet(string sFilename, string sCreatedBy)
        {
            DataTable dtName = new DataTable();
            DataTable dtDescription = new DataTable();
            DataTable dtValueNames = new DataTable();
            DataTable dtValueItems = new DataTable();
            string sql = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sFilename + "';Extended Properties='Excel 12.0 Xml;HDR=No';";
            using (OleDbConnection Con = new System.Data.OleDb.OleDbConnection(sql))
            {
                try
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        Con.Open();
                        cmd.Connection = Con;

                        // Get Test Data Set Name
                        cmd.CommandText = "SELECT * FROM [TestCase$B1:B1]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtName.Load(objDataReader);
                        }

                        // Get Description
                        cmd.CommandText = "SELECT * FROM [TestCase$A3:A3]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtDescription.Load(objDataReader);
                        }

                        // Get Value Names
                        cmd.CommandText = "SELECT * FROM [TestCase$A4:J4]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtValueNames.Load(objDataReader);
                        }

                        // Get Values
                        cmd.CommandText = "SELECT * FROM [TestCase$A5:J100]";
                        using (OleDbDataReader objDataReader = cmd.ExecuteReader())
                        {
                            dtValueItems.Load(objDataReader);
                        }
                        Con.Close();
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }

            // First create the import identifier row
            const string constNewImportTCSQL = @"dbo.PortalAddTestData";
            int iOutIdentity = 0;
            try
            {
                using (SqlCommand cmd = new SqlCommand(constNewImportTCSQL, g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@Name", SqlDbType.VarChar);
                    param.Value = dtName.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@Description", SqlDbType.VarChar);
                    param.Value = dtDescription.Rows[0][0].ToString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@CreatedBy", SqlDbType.VarChar);
                    param.Value = sCreatedBy;
                    cmd.Parameters.Add(param);

                    int iIndex = 0;
                    while( iIndex < 9 )
                    {
                        iIndex++;
                        param = new SqlParameter("@DataPointName0" + iIndex.ToString(), SqlDbType.VarChar);
                        param.Value = dtValueNames.Rows[0][iIndex-1].ToString();
                        cmd.Parameters.Add(param);
                    }

                    SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                    paramOutIdentity.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paramOutIdentity);

                    cmd.ExecuteNonQuery();

                    iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            // Now fill out the steps
            const string constNewImportTSItemSQL = @"dbo.PortalAddTestDataItem";
            try
            {

                int iColCount = dtValueItems.Columns.Count;
                if (iColCount > 9)
                    iColCount = 9;
                int iRowIndex = 0;
                while (iRowIndex < dtValueItems.Rows.Count)
                {
                    if (dtValueItems.Rows[iRowIndex][0].ToString().Trim().Length == 0)
                        break;

                    using (SqlCommand cmd = new SqlCommand(constNewImportTSItemSQL, g_sqlConn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter param;
                        param = new SqlParameter("@ID", SqlDbType.Int);
                        param.Value = iOutIdentity;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@SeqNo", SqlDbType.Int);
                        param.Value = iRowIndex;
                        cmd.Parameters.Add(param);

                        // First handle top row (step name/param names)
                        {
                            int iColIndex = 0;
                            while (iColIndex < iColCount)
                            {
                                param = new SqlParameter("@DataPointData0" + (iColIndex + 1).ToString(), SqlDbType.VarChar);
                                param.Value = dtValueItems.Rows[iRowIndex][iColIndex].ToString();
                                cmd.Parameters.Add(param);
                                iColIndex++;
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                    iRowIndex++;
                }

            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            // Now activate the test data set
            const string constActivateTestDataSQL = @"dbo.PortalActivateTestData";
            try
            {
                using (SqlCommand cmd = new SqlCommand(constActivateTestDataSQL, g_sqlConn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param;

                    param = new SqlParameter("@ID", SqlDbType.Int);
                    param.Value = iOutIdentity;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }


            // Add New Test Case to Import Table
            return "Success";


        }

        public void Dispose()
        {
            Dispose(true);

            // Call SupressFinalize in case a subclass implements a finalizer.
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these  
            // operations, as well as in your methods that use the resource. 
            if (!_disposed)
            {
                if (disposing)
                {
                    g_sqlConn.Close();
                    g_sqlConn.Dispose();
                }

                // Indicate that the instance has been disposed.
                _disposed = true;
            }
        }

    }

   

}